package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.BookDao;
import bookshop.dao.CategoryDao;
import bookshop.dao.DaoFactory;
import bookshop.domain.Book;
import bookshop.domain.Category;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.BookQueryHelper;
import bookshop.utils.DBUtils;

public class BookDaoJDBCImpl implements BookDao {

	private static final String ADD_BOOK = "insert into book values(?,?,?,?,?,?,?,?,?,?)";
	private static final String LOADALL_BOOK = "select * from book order by book_pub_date desc";
	private static final String GET_BOOKCOVER_BYCODE = "select book_image from book where book_code=?";
	private static final String SQL_GET_BYCode = "select * from book where book_code=?";
	private static final String SQL_UPDATE = "update book set book_name=?,book_price=?,book_author=?,book_publisher=?,book_pub_date=?,book_ctg=?,book_desc=?,book_mark=?,book_image=? where book_code=?";
	private static final String SQL_DEL = "delete from book where book_code=?";
	private static final String GETBOOKS_BYCTG = "select * from book where book_ctg=?";

	private String generateSQL(BookQueryHelper helper) {
		
		String sql = "select * from book where 1=1 ";
		
		if (helper.getQryBookCode() != null)
			sql += " and book_code like '%" + helper.getQryBookCode() + "%' ";
		
		if (helper.getQryBookName() != null)
			sql += " and book_name like '%" + helper.getQryBookName() + "%' ";
		
		if (helper.getQryBookPublisher() != null)
			sql += " and book_publisher like '%" + helper.getQryBookPublisher() + "%' ";
		
		if (helper.getQryBookPriceLeft() != null )
			sql += " and book_price >= " + helper.getQryBookPriceLeft()+" ";
		
		if (helper.getQryBookPriceRight() != null )
			sql += " and book_price <= " + helper.getQryBookPriceRight()+" ";
		
		if (helper.getQryBookPubYearLeft() != null )
			sql += " and date_format(book_pub_date,'%Y') >= '" + helper.getQryBookPubYearLeft()+"'";
		
		if (helper.getQryBookPubYearRight() != null )
			sql += " and date_format(book_pub_date,'%Y') <= '" + helper.getQryBookPubYearRight()+"'";
		
		if (helper.getQryBookCtg() != null)
			sql += " and book_ctg=" + helper.getQryBookCtg();
		
		if (helper.getBookPriceSequence() != null){
			
			if(helper.getBookPriceSequence().equals("up")){
				sql += " order by book_price asc";}
			
			if(helper.getBookPriceSequence().equals("down")){
				sql += " order by book_price desc";}
			
		}
		
         
         if (helper.getBookMarkSequence() != null){
 			
 			if(helper.getBookMarkSequence().equals("up")){
 				sql += " order by book_mark asc";
 				
 			}else{
 				sql += " order by book_mark desc";
 			}
 		}
         
         if(helper.getBookPriceSequence() == null&&helper.getBookMarkSequence() == null){
        	 sql += " order by book_code";
         }
		
		
		
		return sql;
		
	}
	
	
	
	
	public void saveBook(Book book) {

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {

			ps = ct.prepareStatement(ADD_BOOK);
			ps.setString(1, book.getBookCode());
			ps.setString(2, book.getBookName());
			ps.setDouble(3, book.getBookPrice());
			ps.setString(4, book.getBookAuthor());
			ps.setString(5, book.getBookPublisher());
			ps.setDate(6, new java.sql.Date(book.getBookPubDate().getTime()));
			ps.setInt(7, book.getCtg().getCtgId());
			ps.setString(8, book.getBookDesc());
			ps.setInt(9, 0);
			ps.setBytes(10, book.getBookImage());

			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书信息保存失败，可能ISBN编号出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	public List<Book> loadall() {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Book> books = new ArrayList<Book>();

		try {
			ps = ct.prepareStatement(LOADALL_BOOK);
			rs = ps.executeQuery();

			while (rs.next()) {

				Book book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);

				books.add(book);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return books;
	}

	public byte[] getBookCover(String bookCode) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		byte[] image = null;

		try {
			ps = ct.prepareStatement(GET_BOOKCOVER_BYCODE);
			ps.setString(1, bookCode);
			rs = ps.executeQuery();

			if (rs.next()) {
				image = rs.getBytes("book_image");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书封面信息获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);

		}
		return image;
	}

	public Book getBookByCode(String bookCode) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Book book = null;

		try {
			ps = ct.prepareStatement(SQL_GET_BYCode);
			ps.setString(1, bookCode);
			rs = ps.executeQuery();

			if (rs.next()) {

				book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return book;
	}

	public void update(Book book) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {

			ps = ct.prepareStatement(SQL_UPDATE);

			ps.setString(1, book.getBookName());
			ps.setDouble(2, book.getBookPrice());
			ps.setString(3, book.getBookAuthor());
			ps.setString(4, book.getBookPublisher());
			ps.setDate(5, new java.sql.Date(book.getBookPubDate().getTime()));
			ps.setInt(6, book.getCtg().getCtgId());
			ps.setString(7, book.getBookDesc());
			ps.setInt(8, book.getBookMark());
			ps.setBytes(9, book.getBookImage());
			ps.setString(10, book.getBookCode());

			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书信息更新失败，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);

		}

	}

	public void delBook(String bookCode) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(SQL_DEL);
			ps.setString(1, bookCode);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书信息删除失败，请检查数据!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);

		}

	}

	public List<Book> getBooksByCtg(Integer ctgId) {

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps= null;
		ResultSet rs = null;

		List<Book> books = new ArrayList<Book>();

		try {
			ps = ct.prepareStatement(GETBOOKS_BYCTG);
			ps.setInt(1, ctgId);
			rs = ps.executeQuery();

			while (rs.next()) {

				Book book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);

				books.add(book);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("根据类别获取图书列表获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return books;
	}


	public List<Book> getBooksByCondition(BookQueryHelper helper) {
		String sql = this.generateSQL(helper);

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Book book = null;
		List<Book> books = new ArrayList<Book>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);

				books.add(book);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return books;
	}

	public Long countBooksByCondition(BookQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) as total");
		sql = sql.substring(0, sql.indexOf("order by"));

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return count;
	}

	public List<Book> getScopedBooks(BookQueryHelper helper, int startIndex,
			int fetchSize) {

		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;

		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Book> books = new ArrayList<Book>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				Book book = new Book();
				book.setBookCode(rs.getString("book_code"));
				book.setBookName(rs.getString("book_name"));
				book.setBookPrice(rs.getDouble("book_price"));
				book.setBookAuthor(rs.getString("book_author"));
				book.setBookPublisher(rs.getString("book_publisher"));
				book.setBookPubDate(new java.util.Date(rs.getDate(
						"book_pub_date").getTime()));
				book.setBookDesc(rs.getString("book_desc"));
				book.setBookMark(rs.getInt("book_mark"));

				CategoryDao ctgDao = (CategoryDao) DaoFactory
						.getDao("categoryDao");
				Category ctg = ctgDao.getCategoryById(rs.getInt("book_ctg"));

				book.setCtg(ctg);

				books.add(book);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书列表获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return books;

	}

}
